package jp.bsws.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import jp.bsws.bean.KengenBean;
import jp.bsws.bean.KeyValueBean;
import jp.bsws.common.BSWSSession;
import jp.bsws.common.BSWSUtils;
import jp.bsws.model.KengenModel;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class KengenDao {
	private static KengenDao kengenDao = new KengenDao();

	/** ログ */
	private Log log = LogFactory.getLog(KengenDao.class);

	/**
	 * コンストラクタ
	 */
	// Singletonパターンで単一のインスタンスを取得
	public static KengenDao getInstance(){
		return kengenDao;
	}

	private String SQL_KENGEN = " SELECT * FROM M_KENGEN ";

	/**
	 * 権限情報取得
	 * 引数のPKがあれば条件とする。
	 */
	public ArrayList<KengenModel> getKengenJoho(Connection con, Integer pkSeq, String pkGroupCod, String pkTenpoCod, String pkKengenCod) throws Exception{

		ArrayList<KengenModel> resultList = new ArrayList<KengenModel>();

		//検索条件の作成
		String whereSeq = "";
		if( pkSeq != null ){
			whereSeq = BSWSUtils.getWhereAnd(whereSeq) + " SEQ = ? ";
		}

		String where = "";
		if(BSWSUtils.isNotBlankNull(whereSeq)){
			where = "  ";
		}
		ArrayList<String> whereList = new ArrayList<String>();
		if( BSWSUtils.isNotBlankNull(pkGroupCod)){
			where += BSWSUtils.getWhereAnd(where) + " GROUP_COD = ? ";
			whereList.add(pkGroupCod);
		}
		if( BSWSUtils.isNotBlankNull(pkKengenCod)){
			where += BSWSUtils.getWhereAnd(where) + " KENGEN_COD = ? ";
			whereList.add(pkKengenCod);
		}

		PreparedStatement stmt = con.prepareStatement(SQL_KENGEN + whereSeq + where );
		int seqCnt = 0;
		if(BSWSUtils.isNotBlankNull(whereSeq)){
			stmt.setInt(1, pkSeq);
			seqCnt++;
		}
		for(int i = 0; i < whereList.size(); i++){
			stmt.setString(i + seqCnt + 1, (String)whereList.get(i));
		}
		ResultSet rs = stmt.executeQuery();

		KengenModel kengenModel = null;
		while(rs.next()){
			kengenModel = new KengenModel();
			kengenModel.setSeq(rs.getInt("SEQ"));
			kengenModel.setStrGroupCod(rs.getString("GROUP_COD"));
			kengenModel.setStrKengenCod(rs.getString("KENGEN_COD"));
			kengenModel.setStrKengenKnj(rs.getString("KENGEN_KNJ"));
			kengenModel.setStrSetteiHenkoKbn(rs.getString("SETTEI_HENKO_KBN"));
			kengenModel.setStrTenpoSansyoKbn(rs.getString("TENPO_SANSYO_KBN"));
			kengenModel.setStrJugyoinSansyoKbn(rs.getString("JUGYOIN_SANSYO_KBN"));
			resultList.add(kengenModel);
		}

		rs.close();
		stmt.close();

		return resultList;
	}

	private String SQL_KENGEN_DELETE =
		" DELETE FROM " +
		"     M_KENGEN " +
		" WHERE " +
		"     SEQ = ? ";

	/**
	 * 権限情報削除
	 */
	public int deleteKengen(Connection con, Integer seq) throws Exception{

		PreparedStatement stmt = con.prepareStatement(SQL_KENGEN_DELETE);
		stmt.setInt(1, seq);

		int deleteCnt = stmt.executeUpdate();

		stmt.close();

		return deleteCnt;
	}

	private String SQL_KENGEN_INSERT =
		" INSERT INTO " +
		"     M_KENGEN ( " +
		"         SEQ " +
		"        ,GROUP_COD " +
		"        ,KENGEN_COD " +
		"        ,KENGEN_KNJ " +
		"        ,SETTEI_HENKO_KBN " +
		"        ,TENPO_SANSYO_KBN " +
		"        ,JUGYOIN_SANSYO_KBN " +
		"        ,TOROKU_TMS " +
		"        ,TOROKUSHA_COD " +
		"        ,KOSHIN_TMS " +
		"        ,KOSHINSHA_COD " +
		"     ) VALUES ( " +
		"        ? " +
		"       ,? " +
		"       ,? " +
		"       ,? " +
		"       ,? " +
		"       ,? " +
		"       ,? " +
		"       ,now() " +
		"       ,? " +
		"       ,now() " +
		"       ,? " +
		"    ) ";

	/**
	 * 権限情報追加
	 */
	public int insertKengen(Connection con, Integer seq, BSWSSession bSWSSession , KengenBean kengenBean) throws Exception{

		BSWSDao bSWSDao = BSWSDao.getInstance();
		//最大値を取得
		Integer maxSeq = bSWSDao.getMaxSeq(con, "M_KENGEN");
		if(seq == null){
			seq = maxSeq + 1;
		}
		PreparedStatement stmt = con.prepareStatement(SQL_KENGEN_INSERT);
		stmt.setInt(1, seq);
		stmt.setString(2, bSWSSession.getStrGroupCod());
		stmt.setString(3, BSWSUtils.hidariUme(String.valueOf(seq), 6, "0"));
		stmt.setString(4, kengenBean.getStrKengenKnj());
		stmt.setString(5, kengenBean.getStrSetteiHenkoKbn());
		stmt.setString(6, kengenBean.getStrTenpoSansyoKbn());
		stmt.setString(7, kengenBean.getStrJugyoinSansyoKbn());
		stmt.setString(8, bSWSSession.getStrJugyoinCod());
		stmt.setString(9, bSWSSession.getStrJugyoinCod());

		int cnt = stmt.executeUpdate();

		stmt.close();

		return cnt;
	}

	private String SQL_KENGEN_UPDATE =
		" UPDATE " +
		"     M_KENGEN " +
		" SET " +
		"         KENGEN_KNJ = ? " +
		"        ,SETTEI_HENKO_KBN = ? " +
		"        ,TENPO_SANSYO_KBN = ? " +
		"        ,JUGYOIN_SANSYO_KBN = ? " +
		"        ,KOSHIN_TMS = now() " +
		"        ,KOSHINSHA_COD = ? " +
		" WHERE " +
		"     SEQ = ? ";

	/**
	 * 店舗情報更新
	 */
	public int updateKengen(Connection con,BSWSSession bSWSSession , KengenBean kengenBean) throws Exception{

		PreparedStatement stmt = con.prepareStatement(SQL_KENGEN_UPDATE);

		stmt.setString(1, kengenBean.getStrKengenKnj());
		stmt.setString(2, kengenBean.getStrSetteiHenkoKbn());
		stmt.setString(3, kengenBean.getStrTenpoSansyoKbn());
		stmt.setString(4, kengenBean.getStrJugyoinSansyoKbn());
		stmt.setString(5, bSWSSession.getStrJugyoinCod());
		stmt.setInt(6, kengenBean.getSeq());

		int cnt = stmt.executeUpdate();

		stmt.close();

		return cnt;
	}

	private String SQL_KV = " SELECT SEQ,KENGEN_KNJ FROM M_KENGEN WHERE GROUP_COD = ? ";

	/**
	 * 店舗に登録されている権限情報取得
	 * 引数のPKがあれば条件とする。
	 */
	public List<KeyValueBean> getKengenList(Connection con, String pkGroupCod, String pkTenpoCod) throws Exception{
		List<KeyValueBean> resultList = new ArrayList<KeyValueBean>();

		//検索条件の作成
		String where = "";
		if( BSWSUtils.isNotBlankNull(pkTenpoCod)){
			where += " AND TENPO_COD = ? ";
		}

		PreparedStatement stmt = con.prepareStatement(SQL_KV + where);

		stmt.setString(1, pkGroupCod);
		if( BSWSUtils.isNotBlankNull(pkTenpoCod)){
			stmt.setString(2, pkTenpoCod);
		}
		ResultSet rs = stmt.executeQuery();

		KeyValueBean kvBean = null;
		while(rs.next()){
			 kvBean = new KeyValueBean();
			String key = rs.getString("SEQ");
			String value = rs.getString("KENGEN_KNJ");
			kvBean.setKey(key);
			kvBean.setValue(value);
			resultList.add(kvBean);
		}
		rs.close();
		stmt.close();

		return resultList;
	}
}